package com.ustadzmenjawab.crawler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ustadzmenjawab.server.DBConnFactory;
import com.ustadzmenjawab.shared.Article;

public class CrawlerDB {
	private Connection conn;

	public CrawlerDB(){
		conn = DBConnFactory.getConnectionWODataSource();
	}

	public void insertArticle(Article article){
		try {
			PreparedStatement ps = conn.prepareStatement("insert into ustadz_menjawab(judul, ustadz, artikel, sumber, tahun, url, kategori,pertanyaan) Values (?,?,?,?,?,?,?,?)");
			ps.setString(1, article.getJudul());
			ps.setString(2, article.getUstadz());
			ps.setString(3, article.getArtikel());
			ps.setString(4, article.getSumber());
			ps.setString(5, article.getTahun());
			ps.setString(6, article.getUrl());
			ps.setString(7, article.getKategori());
			ps.setString(8, article.getPertanyaan());
			ps.executeUpdate();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{

		}
	}
	
	public boolean isURLExist(String url){
		ResultSet rs =null;
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement("Select url from ustadzmenjawabdb_prod where url = ?");
			ps.setString(1,url);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
			ps.close();
			ps = conn.prepareStatement("Select url from ustadz_menjawab where url = ?");
			ps.setString(1,url);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
			
			return false;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return false;
	}
	
	public boolean isTitleExist(String title, String source){
		ResultSet rs =null;
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement("Select url from ustadzmenjawabdb_prod where judul = ? and sumber = ?");
			ps.setString(1,title);
			ps.setString(2,source);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
			ps.close();
			ps = conn.prepareStatement("Select url from ustadz_menjawab where judul = ? and sumber = ?");
			ps.setString(1,title);
			ps.setString(2,source);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
			
			return false;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return false;
	}
	
	public void insertFailure(String url, String msg){
		try{
			PreparedStatement ps = conn.prepareStatement("insert into failed_url(url,failure_message) values(?,?)");
			ps.setString(1, url);
			ps.setString(2, msg);
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	
}
